package es.udc.fbellas.j2ee.tiendaweb.model.tarjetabancaria.DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import es.udc.fbellas.j2ee.tiendaweb.model.tarjetabancaria.TO.TarjetaBancariaTO;
import es.udc.fbellas.j2ee.util.exceptions.DuplicateInstanceException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

/**
 * An implementation of <code>SQLAccountDAO</code> for databases providing
 * counter columns with JDBC 3 drivers.
 */
public class SQLTarjetaBancariaDAO {

/* comprobar si existe una tarjeta bancaria buscando por el número de tarjeta */

    public boolean exists(Connection connection, Long nTarjeta) 
        throws InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            /* Create "preparedStatement". */
            String queryString = "SELECT NUMTARJETA FROM TARJETABANCARIA WHERE NUMTARJETA = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setLong(i++, nTarjeta.longValue());
            
            /* Execute query. */
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
        
    }


/* crear nueva tarjeta bancaria a partir de su numero de tarjeta y su fecha de expiración */

    public TarjetaBancariaTO create(Connection connection, TarjetaBancariaTO tarjetaBancariaTO) 
        throws InternalErrorException, DuplicateInstanceException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
    
        try {
                
            /* Create "preparedStatement". */
            String queryString = "INSERT INTO TARJETABANCARIA" +
                " (NUMTARJETA, FECHAEXP) VALUES (?, ?)";
            preparedStatement = connection.prepareStatement(queryString,
                Statement.RETURN_GENERATED_KEYS);    
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setLong(i++, 
                tarjetaBancariaTO.getNumTarjeta());
            preparedStatement.setDate(i++, tarjetaBancariaTO.getFecha());
                        
            /* Execute query. */
            int insertedRows = preparedStatement.executeUpdate();
        
            if (insertedRows == 0) {
                throw new SQLException("No se puede añadir a la tabla" +
                    " 'TARJETABANCARIA'");
            }

            if (insertedRows > 1) {
                throw new SQLException("Fila Duplicada en la tabla 'TARJETABANCARIA'");
            }
            
            /* Get account identifier. */
//            resultSet = preparedStatement.getGeneratedKeys();

//            if (!resultSet.next()) {
//                throw new InternalErrorException(new SQLException(
//                    "JDBC driver did not return generated key."));
//            }
//            Int numTarjeta = new Int(resultSet.getInt(1)); 
            
            /* Return the value object. */
            return new TarjetaBancariaTO(tarjetaBancariaTO.getNumTarjeta(), tarjetaBancariaTO.getFecha());
        
        } catch (SQLException e) {
            throw new InternalErrorException(e);

        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }
        
    }


/* encontrar tarjeta bancaria a partir de su número */

    public TarjetaBancariaTO find(Connection connection, Long numTarjeta) 
        throws InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
    
        try {
                
            /* Create "preparedStatement". */
            String queryString = "SELECT NUMTARJETA, FECHAEXP FROM TARJETABANCARIA" + " WHERE NUMTARJETA = ?";
            preparedStatement = connection.prepareStatement(queryString);    
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setLong(i++, 
                numTarjeta.longValue());
                                    
            /* Execute query. */
			resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InternalErrorException(new SQLException(
                    "No hay ninguna tarjeta bancaria con ese numero."));
            }

			/* get result */
			i = 1;
			Long numT = resultSet.getLong(i++);
			Date fExp = resultSet.getDate(i++);


			return new TarjetaBancariaTO(numT, fExp);
        
        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
			GeneralOperations.closeResultSet(resultSet);
        }
        
    }
    
    
    /* This class can by tested by "SQLAccountDAOFactory". */

}
